using Microsoft.Data.Sqlite;
using Dapper;
using WpfApp1.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;

namespace WpfApp1.Services
{
    /// <summary>
    /// SQLite数据库服务类
    /// </summary>
    public class DatabaseService
    {
        private readonly string _connectionString;

        public DatabaseService()
        {
            string dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tcp_data.db");
            _connectionString = $"Data Source={dbPath}";
            InitializeDatabaseAsync().Wait();
        }

        /// <summary>
        /// 初始化数据库
        /// </summary>
        private async Task InitializeDatabaseAsync()
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            // 创建表
            var createTableSql = @"
                CREATE TABLE IF NOT EXISTS TcpDataRecords (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ServerIp TEXT NOT NULL,
                    ServerPort INTEGER NOT NULL,
                    Command TEXT NOT NULL,
                    ExtractedData TEXT,
                    RawResponse TEXT,
                    CreatedAt TEXT NOT NULL,
                    IsValid INTEGER NOT NULL
                )";

            await connection.ExecuteAsync(createTableSql);
        }

        /// <summary>
        /// 保存TCP数据记录
        /// </summary>
        /// <param name="record">数据记录</param>
        /// <returns>保存的记录ID</returns>
        public async Task<int> SaveTcpDataRecordAsync(TcpDataRecord record)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var insertSql = @"
                INSERT INTO TcpDataRecords (ServerIp, ServerPort, Command, ExtractedData, RawResponse, CreatedAt, IsValid)
                VALUES (@ServerIp, @ServerPort, @Command, @ExtractedData, @RawResponse, @CreatedAt, @IsValid);
                SELECT last_insert_rowid();";

            var id = await connection.ExecuteScalarAsync<int>(insertSql, record);
            return id;
        }

        /// <summary>
        /// 获取所有TCP数据记录
        /// </summary>
        /// <returns>数据记录列表</returns>
        public async Task<List<TcpDataRecord>> GetAllTcpDataRecordsAsync()
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT Id, ServerIp, ServerPort, Command, ExtractedData, RawResponse, CreatedAt, IsValid
                FROM TcpDataRecords
                ORDER BY CreatedAt DESC";

            var records = await connection.QueryAsync<TcpDataRecord>(selectSql);
            return records.AsList();
        }

        /// <summary>
        /// 获取最近的TCP数据记录
        /// </summary>
        /// <param name="count">记录数量</param>
        /// <returns>数据记录列表</returns>
        public async Task<List<TcpDataRecord>> GetRecentTcpDataRecordsAsync(int count = 10)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT Id, ServerIp, ServerPort, Command, ExtractedData, RawResponse, CreatedAt, IsValid
                FROM TcpDataRecords
                ORDER BY CreatedAt DESC
                LIMIT @Count";

            var records = await connection.QueryAsync<TcpDataRecord>(selectSql, new { Count = count });
            return records.AsList();
        }

        /// <summary>
        /// 删除所有数据记录
        /// </summary>
        public async Task ClearAllRecordsAsync()
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var deleteSql = "DELETE FROM TcpDataRecords";
            await connection.ExecuteAsync(deleteSql);
        }

        /// <summary>
        /// 检查是否存在相同的ExtractedData
        /// </summary>
        /// <param name="extractedData">要检查的提取数据</param>
        /// <returns>如果存在相同数据返回true，否则返回false</returns>
        public async Task<bool> ExistsExtractedDataAsync(string extractedData)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT COUNT(*) 
                FROM TcpDataRecords 
                WHERE ExtractedData = @ExtractedData";

            var count = await connection.ExecuteScalarAsync<int>(selectSql, new { ExtractedData = extractedData });
            return count > 0;
        }

        /// <summary>
        /// 检查是否存在相同的ExtractedData（忽略大小写）
        /// </summary>
        /// <param name="extractedData">要检查的提取数据</param>
        /// <returns>如果存在相同数据返回true，否则返回false</returns>
        public async Task<bool> ExistsExtractedDataIgnoreCaseAsync(string extractedData)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT COUNT(*) 
                FROM TcpDataRecords 
                WHERE LOWER(ExtractedData) = LOWER(@ExtractedData)";

            var count = await connection.ExecuteScalarAsync<int>(selectSql, new { ExtractedData = extractedData });
            return count > 0;
        }

        /// <summary>
        /// 获取所有相同的ExtractedData记录
        /// </summary>
        /// <param name="extractedData">要查找的提取数据</param>
        /// <returns>包含相同数据的记录列表</returns>
        public async Task<List<TcpDataRecord>> GetRecordsByExtractedDataAsync(string extractedData)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT Id, ServerIp, ServerPort, Command, ExtractedData, RawResponse, CreatedAt, IsValid
                FROM TcpDataRecords
                WHERE ExtractedData = @ExtractedData
                ORDER BY CreatedAt DESC";

            var records = await connection.QueryAsync<TcpDataRecord>(selectSql, new { ExtractedData = extractedData });
            return records.AsList();
        }

        /// <summary>
        /// 获取所有相同的ExtractedData记录（忽略大小写）
        /// </summary>
        /// <param name="extractedData">要查找的提取数据</param>
        /// <returns>包含相同数据的记录列表</returns>
        public async Task<List<TcpDataRecord>> GetRecordsByExtractedDataIgnoreCaseAsync(string extractedData)
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT Id, ServerIp, ServerPort, Command, ExtractedData, RawResponse, CreatedAt, IsValid
                FROM TcpDataRecords
                WHERE LOWER(ExtractedData) = LOWER(@ExtractedData)
                ORDER BY CreatedAt DESC";

            var records = await connection.QueryAsync<TcpDataRecord>(selectSql, new { ExtractedData = extractedData });
            return records.AsList();
        }

        /// <summary>
        /// 获取重复的ExtractedData统计信息
        /// </summary>
        /// <returns>重复数据的统计信息</returns>
        public async Task<List<object>> GetDuplicateExtractedDataStatsAsync()
        {
            using var connection = new SqliteConnection(_connectionString);
            await connection.OpenAsync();

            var selectSql = @"
                SELECT ExtractedData, COUNT(*) as Count, MIN(CreatedAt) as FirstOccurrence, MAX(CreatedAt) as LastOccurrence
                FROM TcpDataRecords
                WHERE ExtractedData IS NOT NULL AND ExtractedData != ''
                GROUP BY ExtractedData
                HAVING COUNT(*) > 1
                ORDER BY COUNT(*) DESC";

            var results = await connection.QueryAsync(selectSql);
            return results.ToList();
        }
    }
}
